# mysql 中的存储过程
# 1. 什么是存储过程?
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
MySQL5.0 版本开始支持存储过程。
大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有的操作都那么简单。经常会有一个完整的操作需要多条语句才能完成。
存储过程简单来说,就是为以后的使用而保存的一条或多条 MySQL 语句的集合。可将其视为批处理文件。虽然他们的作用不仅限于批处理。
1
2
3
4
5
2
3
4
5
# 2. 存储过程的使用
# 2.1 mysql 中的变量
1) 系统变量
系统变量那肯定就是mysql数据库为我们提供的,再细化的话又可以分为两种:全局变量和会话变量。
自定变量就是用户自己定义的,而不是由系统提供的。自定义变量也可以分为两种:用户变量和局部变量。
1
2
3
2
3
2) 查看所有的系统变量
mysql> show global variables;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
................................................
-- 模糊查询
mysql> show global variables like 'auto%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_generate_certs | ON |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
+--------------------------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
3) 查看某个指定的系统变量
-- select @@global.变量名称;查看会话的变量值的时候只需要把global变成session就可以了
mysql> select @@global.version_comment;
+--------------------------+
| @@global.version_comment |
+--------------------------+
| Source distribution |
+--------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
4) 为某个变量赋值
set global 系统变量名 = 新值;
set @@global.系统变量名 = 新值;
1
2
2
5) 声明初始化
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 :=值
/*
mysql :=和=的区别
1) 执行set和update语句时,这两个符号都是赋值的意思
2) 执行select语句时,用:=赋值后,相当于set+select,该变量会在当前会话中永久显示成新值;用=赋值后,再次查询该变量,还会是原来的值
*/
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
6) 局部变量
-- 1) 声明
declare 变量名 类型;
declare 变量名 类型 default 默认值
-- 2) 赋值
set @用户变量名 = 值
set @用户变量名 := 值
select @用户变量名 :=值
-- select 字段 into 自定义变量 from 表名
/*
该语法在mysql中不支持,可以使用create table + insert into
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 2.2 存储过程的创建
-- CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 参数类型...]) [特性 …] 过程体
CREATE PROCEDURE sp_name ([IN |OUT |INOUT ] $param $param_type) [$characteristic …] $routine_body
-- IN 输入参数:表示调用者向过程传入值
-- OUT 输出参数:表示过程向调用者传出值
-- INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值
-- $param_type :表示任何有效的MySQL数据类型
-- $characteristic :表示
-- | LANGUAGE SQL: 说明'过程体'部分是由SQL语言的语句组成,这也是数据库系统默认的语言
-- | [NOT] DETERMINISTIC: 指明存储过程的执行结果是否是确定的。
-- | DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出
-- | NOT DETERMINISTIC表示结果是非确定的,相同的输入可能得到不同的输出。
-- | 默认情况下,结果是非确定的。
-- | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }: 指明子程序使用SQL语句的限制
-- | SQL SECURITY { DEFINER | INVOKER }: 指明谁有权限来执行
-- | COMMENT 'string': 注释信息
-- $routine_body: 表示有效的SQL过程语句或语句,又叫'过程体'.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
characteristic值含义
1) NOT DETERMINISTIC :如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC。
2) CONTAINS SQL :表示子程序不包含读或写数据的语句
3) NO SQL表示子程序不包含SQL语句
4) READS SQL DATA表示子程序包含读数据的语句,但不包含写数据的语句
5) MODIFIES SQL DATA表示子程序包含写数据的语句
6) 如果这些特征没有明确给定,默认的是CONTAINS SQL
7) SQL SECURITY特征可以用来指定 子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。
1
2
3
4
5
6
7
2
3
4
5
6
7
in/out/inout的使用
/*-----------------------------------------------------------------------------*/
/*------------创建一个无参数存储过程---------------------------------------------*/
DELIMITER $$
CREATE PROCEDURE test1()
BEGIN
SELECT * FROM test;
END$$
DELIMITER $$
-- 1.声明语句结束符$$
DELIMITER $$
-- 2.声明存储过程:
CREATE PROCEDURE test1()
-- 3. 存储过程开始和结束符号
BEGIN
......
END$$
/*-----------------------------------------------------------------------------*/
/*------------创建一个in 输入参数的存储过程---------------------------------------*/
DELIMITER $$
CREATE PROCEDURE test2(IN pr_id INT)
BEGIN
SELECT * FROM test WHERE id=pr_id;
END$$
DELIMITER $$
-- 调用b的存储过程
CALL test2(2);
-- 删除存储过程
DROP PROCEDURE test2;
/*-----------------------------------------------------------------------------*/
/*------------out 的存储过程的说法----------------------------------------------*/
DELIMITER $$
CREATE PROCEDURE test3(OUT pr_name VARCHAR(20))
BEGIN
SELECT NAME INTO pr_name FROM a WHERE id=2;
END$$
DELIMITER $$
-- 调用test3的存储过程 out是输出,所以调用存储过程时这是一个变量@name
CALL test3(@name);
-- 然后显示值
SELECT @name
/*-----------------------------------------------------------------------------*/
/*------------inout 的存储过程的说明--------------------------------------------*/
DELIMITER$$
CREATE PROCEDURE test4(INOUT pr_name VARCHAR(20))
BEGIN
SELECT NAME INTO pr_name FROM a WHERE id=pr_name;
END$$
DELIMITER $$
/*调用inout的存储过程
inout是可以接受一个参数并输出一个参数
*/
/*设置一个参数,@id=5 说明输入的参数为5*/
SET @id=0;
-- 然后在调用返回输出一个值
CALL test4(@id);
-- 显示存储过程
SELECT @id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# 2.3 存储函数的基本语法
/*存储过程体*/
BEGIN
BEGIN
BEGIN
statements;
END
END
END
/*参数申明*/
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
/*变量赋值*/
SET 变量名 = 表达式值 [,variable_name = expression ...]
/*if-then-else 语句*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test5(IN parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if var=0 then
-> insert into t values(666);
-> end if;
-> if parameter=0 then
-> update t set s1=s1+1;
-> else
-> update t set s1=s1+2;
-> end if;
-> end;
-> $$
mysql > DELIMITER ;
/*case语句*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test6 (in parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;
-> end;
-> $$
mysql > DELIMITER ;
/*while ···· end while*/
/*
while 条件 do
--循环体
end while
*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test7()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end;
-> $$
mysql > DELIMITER ;
/*repeat···· end repeat*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test8 ()
-> begin
-> declare v int;
-> set v=0;
-> repeat
-> insert into t values(v);
-> set v=v+1;
-> until v>=5
-> end repeat;
-> end;
-> $$
mysql > DELIMITER ;
/* loop ·····endloop*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test9 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> insert into t values(v);
-> set v=v+1;
-> if v >=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> $$
mysql > DELIMITER ;
/*ITERATE迭代*/
mysql > DELIMITER $$
mysql > CREATE PROCEDURE test10 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> if v=3 then
-> set v=v+1;
-> ITERATE LOOP_LABLE;
-> end if;
-> insert into t values(v);
-> set v=v+1;
-> if v>=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> $$
mysql > DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
# 3. 存储过程的优缺点
# 3.1 存储过程的优点
1) 封装性
存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。
2) 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
3) 可减少网络流量
由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。
4) 高性能
存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。
5) 提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 3.2 存储过程的缺点
1) 如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外,如果您在存储过程中过度使用大量逻辑操作,则 CPU 使用率也会增加,因为 MySQL 数据库最初的设计侧重于高效的查询,不利于逻辑运算;
2) 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难;
3) 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是,MySQL 不提供调试存储过程的功能;
4) 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。
1
2
3
4
5
6
7
2
3
4
5
6
7